
package edu.tongji.jiaowu.dao.jdbc;
 
import java.util.ArrayList;
import java.util.List;

import edu.tongji.jiaowu.dao.ExternalExamStudentsDao;
import com.tongji.quickCRUDQ.define.Row;
import com.tongji.quickCRUDQ.model.CommonBean;
import com.tongji.quickCRUDQ.dao.jdbc.BaseDaoJdbc;

public class ExternalExamStudentsDaoJdbc extends BaseDaoJdbc implements ExternalExamStudentsDao {
	String tableName="T_EXTERNAL_EXAM_STUDENTS";
	String beanName="externalExamStudents";
	
	public CommonBean createExternalExamStudents(){
		CommonBean externalExamStudentss=this.createBean(tableName,beanName);
                return externalExamStudentss;		
	}
	
/*
	public CommonBean findExternalExamStudentss(String refid) {
		CommonBean externalExamStudentss=this.createExternalExamStudents();
		String sql="select * from T_EXTERNAL_EXAM_STUDENTS where refid=?";
		List data=this.getCommonDao().select(sql,new String[]{refid});
		externalExamStudentss.addData(data);
                return externalExamStudentss;
       }
*/
	public CommonBean findExternalExamStudentsById(String id) {
		CommonBean externalExamStudentss=this.createExternalExamStudents();
		String sql="select * from T_EXTERNAL_EXAM_STUDENTS where id=?";
		List data=this.getCommonDao().select(sql,new String[]{id});
		externalExamStudentss.addData(data);
    return externalExamStudentss;
  }
	
	public CommonBean findExternalExamStudentsByStudentNo(String studentNo, String year, String semester) {
		CommonBean student=createBean("V_EXTERNAL_EXAM_STUDENTS", "externalExamStudentsDetail");
		String sql="select * from V_EXTERNAL_EXAM_STUDENTS t where student_no=? and school_year=? and school_semester=? and exists( \n"+
                   " select * from t_external_exam_type  where entry_status='1' and exam_type=t.exam_type \n"+
                   " and school_year=t.school_year and school_semester=t.school_semester ) ";
		List data=getCommonDao().select(sql, new String[]{studentNo,year,semester});
		student.addData(data);
		return student;
	}
  
	public CommonBean findAllExternalExamStudentss() {
		CommonBean externalExamStudentss=this.createExternalExamStudents();
		String sql="select * from T_EXTERNAL_EXAM_STUDENTS ";
		List data=this.getCommonDao().select(sql,new String[]{});
		externalExamStudentss.addData(data);
       return externalExamStudentss;
  }
	public void insertExternalExamStudents(CommonBean externalExamStudentsBean){
		List data=externalExamStudentsBean.getData();
		Row row=this.getRow(tableName);
		this.getCommonDao().templateBatchInsert(row,data,this.getIdGenerator());
	}
	public void updateExternalExamStudents(CommonBean externalExamStudentsBean){
		List data=externalExamStudentsBean.getData();
		Row row=this.getRow(tableName);
		this.getCommonDao().templateBatchUpdate(row,data);
	}

	public CommonBean findTypes(String currentYear, String currentSemester, String studentNo) {
		CommonBean externalExamStudents=this.createExternalExamStudents();
		String sql="select * from T_EXTERNAL_EXAM_STUDENTS where school_year=? and school_semester=? and student_no=? ";
		List data=this.getCommonDao().select(sql,new String[]{currentYear,currentSemester,studentNo});
		externalExamStudents.addData(data);
       return externalExamStudents;
    }

	public void remove(String id) {
		String sql="delete from T_EXTERNAL_EXAM_STUDENTS where id=?";
		getCommonDao().updateRecord(sql, new String[]{id});
	}

	public List entryDepartments(String currentYear, String currentSemester, String currentType) {
		String params[]=new String[]{currentYear,currentSemester};
		String sql="select distinct college_department,college_name  from v_external_exam_students  where school_year=? and school_semester=? ";
		if (!"".equals(currentType)) {
			sql+=" and exam_type=? ";
			params=new String[]{currentYear,currentSemester,currentType};
		}
		sql+=" order by college_department ";
		List data=getCommonDao().select(sql, params);
		return data;
	}

	public List entryMajors(String currentYear, String currentSemester, String currentType, String currentDepartment) {
		String params[]=new String[]{currentYear,currentSemester,currentDepartment};
		String sql="select distinct major,major_name from v_external_exam_students  where school_year=? and school_semester=? and college_department=? ";
		if (!"".equals(currentType)) {
			sql+=" and exam_type=? ";
			params=new String[]{currentYear,currentSemester,currentDepartment,currentType};
		}
		sql+=" order by major ";
		List data=getCommonDao().select(sql, params);
		return data;
	}

	public List statisticsDestail(String currentYear, String currentSemester, String currentType, String currentDepartment, String currentMajor) {
		List list=new ArrayList();
		list.add(currentYear);
		list.add(currentSemester);
		String sql="select grade,college_department,college_name,major,major_name,exam_type,exam_name,class_no,class_name,"+ 
                   " sum(case status when 'Y' then 1 else 0 end) payed,sum(case status when 'N' then 1 else 0 end) unpay "+
                   " from v_external_exam_students  where school_year= ?  and school_semester= ? ";
		if (!"".equals(currentType)) {
			sql+=" and exam_type=? ";
			list.add(currentType);
		}
		if (!"".equals(currentDepartment)) {
			sql+=" and college_department=? ";
			list.add(currentDepartment);
		}
		if (!"".equals(currentMajor)) {
			sql+=" and major=? ";
			list.add(currentMajor);
		}
		sql+=" group by grade,college_department,college_name,major,major_name,exam_type,exam_name,class_no,class_name order by grade,college_department,major,exam_type";
		String params[]=new String[list.size()];
		list.toArray(params);
		List data=getCommonDao().select(sql, params);
		return data;
	}

	public List findStudentList(String year, String semester, String examType) {
		String sql="select student_no from T_EXTERNAL_EXAM_STUDENTS where school_year=? and school_semester=? and exam_type=?  ";
		List list=getCommonDao().selectOneFieldList(sql, new String[]{year,semester,examType});
		return list;
	}


}

